-- @owner:魏亚军
-- @date:2023-12-18
-- @testpoint:3.在事务中修改序列是否循环，部分测试点合理报错

--step1:创建非循环序列，最小值为1，最大值为2，其他属性为默认值;expect:成功
drop sequence if exists seqopengauss_alter_sequence_cycle_case0003_1;
create sequence seqopengauss_alter_sequence_cycle_case0003_1 minvalue 1 maxvalue 2 nocycle;

--step2:查看当前序列是否循环;expect:成功(1 row)
select is_cycled from seqopengauss_alter_sequence_cycle_case0003_1;

--step3:激活当前序列;expect:成功(1 row)
select nextval('seqopengauss_alter_sequence_cycle_case0003_1');

--step4:查看设置是否生效;expect:成功(1 row)
select nextval('seqopengauss_alter_sequence_cycle_case0003_1');

--step5:查看设置是否生效;expect:报错:ERROR:reached maximum
select nextval('seqopengauss_alter_sequence_cycle_case0003_1');

--step6:在事务中修改序列为循环序列
begin 
alter sequence seqopengauss_alter_sequence_cycle_case0003_1 cycle;
end;
/

--step7:查看当前序列是否循环;expect:成功(1 row)
select is_cycled from seqopengauss_alter_sequence_cycle_case0003_1;

--step8:验证修改是否生效;expect:成功(1 row)
select nextval('seqopengauss_alter_sequence_cycle_case0003_1');

--step9:验证修改是否生效;expect:成功(1 row)
select nextval('seqopengauss_alter_sequence_cycle_case0003_1');

--step10:验证修改是否生效;expect:成功(1 row)
select nextval('seqopengauss_alter_sequence_cycle_case0003_1');

--step11:在事务中修改序列为非循环序列;expect:成功
begin;
alter sequence seqopengauss_alter_sequence_cycle_case0003_1 nocycle;
--step15:退出事务
end;
/

--step16:查看修改是否生效;expect:成功(1 row)
select is_cycled from seqopengauss_alter_sequence_cycle_case0003_1;

--step17:验证修改是否生效;expect:成功(1 row)
select nextval('seqopengauss_alter_sequence_cycle_case0003_1');

--step18:验证修改是否生效;expect:报错:ERROR:reached maximum
select nextval('seqopengauss_alter_sequence_cycle_case0003_1');

--step19:清理环境;except:成功
drop sequence seqopengauss_alter_sequence_cycle_case0003_1;